package com.dao;

import com.model.JobInfo;
import com.utils.from.BackJobInfoListForm;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface JobInfoRepository extends JpaRepository<JobInfo, Long>, JpaSpecificationExecutor {

    @Query(value =
            "select  recruit_title, min_wage, max_wage, release_date, exp_range, recruitment_num, " +
                    "company_name, industry, work_address, capture_url " +
                    "from job_info " +
                    "where 1 = 1 " +
                    "and (recruit_title LIKE CONCAT('%',:#{#form.title},'%') OR :#{#form.title} IS NULL) " +
                    "and (release_date >= :#{#form.startDate} OR :#{#form.startDate} IS NULL) " +
                    "and (release_date <= :#{#form.endDate} OR :#{#form.endDate} IS NULL) " +
                    "and (reptile_class = :#{#form.reptileClass} OR :#{#form.reptileClass} IS NULL) " +
                    "order by release_date",
            nativeQuery=true)
    Page<Map<String, Object>> selectByFrom(@Param("form") BackJobInfoListForm form, Pageable pageable);


    @Query(value = "select id, release_date as releaseDate from job_info where hashcode = :hashcode limit 0,1 for update", nativeQuery=true)
    Map<String, Object> isExist(@Param("hashcode") String hashcode);

    @Query(
            value =
            "select " +
                    "id, " +
                    "recruit_title as recruitTitle, " +
                    "company_name as companyName, " +
                    "work_address as workAddress, " +
                    "lng, lat, min_wage as minWage, max_wage as maxWage, " +
                    "academic_requirements as academicRequirements, " +
                    "release_date as releaseDate, " +
                    "capture_url as captureUrl " +
            "from job_info",
            nativeQuery=true
    )
    List<Map<String, Object>> getSimpleData();

    @Query(
            value = "select " +
                    "id, " +
                    "recruit_title as recruitTitle, " +
                    "company_name as companyName, " +
                    "work_address as workAddress, " +
                    "lng, lat, min_wage as minWage, max_wage as maxWage, " +
                    "academic_requirements as academicRequirements, " +
                    "release_date as releaseDate, " +
                    "capture_url as captureUrl, " +
                    "have_read as haveRead " +
                    "from job_info " +
                    "WHERE 1=1 " +
                    "and (release_date >= :startDate OR :startDate IS NULL) " +
                    "and (release_date <= :endDate OR :endDate IS NULL) " +
                    "and (recruit_title LIKE CONCAT('%',:title,'%') OR :title IS NULL) " +
                    "order by release_date",
            nativeQuery=true
    )
    List<Map<String, Object>> searchSimpleInfo(@Param("startDate") String startDate,
                                               @Param("endDate") String endDate,
                                               @Param("title") String title);

    @Modifying
    @Query(
            value = "UPDATE job_info set have_read = true where id = :id",
            nativeQuery = true)
    int setHaveRead(@Param("id") Long id);
}
